import streamlit as st
from sqlalchemy import create_engine
import pandas as pd
import os
import numpy as np
import matplotlib.pyplot as plt
from wordcloud import WordCloud
from sklearn.cluster import KMeans
from matplotlib.font_manager import FontProperties

# 设置页面标题
st.title('Bilibili视频历史数据分析')

# 获取数据库路径
base_dir = os.path.dirname(__file__)
database_path = os.path.join(base_dir, '..', 'coach', 'database', 'coach.db')
print(database_path)

# 使用SQLAlchemy创建数据库引擎
engine = create_engine(f'sqlite:///{database_path}')
query = "SELECT * FROM video_records"

@st.cache_data
def load_data(query, _engine):
    df = pd.read_sql_query(query, _engine)
    return df

# 加载数据
data = load_data(query, engine)

# 数据清理
data['view_at'] = pd.to_datetime(data['view_at'], unit='s')
data['progress'] = data['progress'].astype(int)
data['duration'] = data['duration'].astype(int)
data['is_finish'] = data['is_finish'].astype(bool)
data['is_fav'] = data['is_fav'].astype(bool)

# 展示数据表格
st.write("以下是从SQLite数据库中加载的视频历史记录：")
st.dataframe(data)

# 描述性统计
st.subheader('描述性统计')
st.write(data.describe())

# 按标签的视频数量分布
st.subheader("按标签的视频数量分布：")
tag_counts = data['tag_name'].value_counts()
st.bar_chart(tag_counts)

# 按作者的视频数量分布
st.subheader("按作者的视频数量分布：")
author_counts = data['author_name'].value_counts().head(10)
st.bar_chart(author_counts)

# 按观看时间分布
st.subheader("按观看时间分布：")
view_at_counts = data['view_at'].dt.date.value_counts().sort_index()
st.line_chart(view_at_counts)

# 生成词云
st.subheader("视频标题词云")
text = ' '.join(data['title'].astype(str).tolist())

# 加载中文字体
font_path = 'coach/utils/font/LXGWWenKaiMono-Regular.ttf'  # 替换为实际的中文字体路径
wordcloud = WordCloud(font_path=font_path, width=800, height=400, background_color='white').generate(text)

fig, ax = plt.subplots(figsize=(10, 5))
ax.imshow(wordcloud, interpolation='bilinear')
ax.axis('off')
st.pyplot(fig)

# 完成情况分析
st.subheader("视频完成情况分析")
finish_counts = data['is_finish'].value_counts()

fig, ax = plt.subplots()
ax.pie(finish_counts, labels=finish_counts.index, autopct='%1.1f%%', startangle=90)
ax.axis('equal')  # 保持饼图为圆形
st.pyplot(fig)

# 收藏情况分析
st.subheader("视频收藏情况分析")
fav_counts = data['is_fav'].value_counts()

fig, ax = plt.subplots()
ax.pie(fav_counts, labels=fav_counts.index, autopct='%1.1f%%', startangle=90)
ax.axis('equal')
st.pyplot(fig)

# 观看时长分析
st.subheader("视频观看时长分布")
fig, ax = plt.subplots()
ax.hist(data['progress'], bins=50)
st.pyplot(fig)

# 高级分析 - 聚类分析
st.subheader("高级分析 - 聚类分析")
X = data[['progress', 'duration']].dropna()
kmeans = KMeans(n_clusters=3, random_state=0).fit(X)
data['cluster'] = kmeans.labels_
st.write("聚类分析结果：")
st.dataframe(data[['title', 'progress', 'duration', 'cluster']])

# 可视化聚类结果
st.subheader("聚类结果可视化")
for cluster in sorted(data['cluster'].unique()):
    cluster_data = data[data['cluster'] == cluster]
    st.write(f"Cluster {cluster}:")
    st.dataframe(cluster_data[['title', 'progress', 'duration']])
